﻿
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Xml;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using EQT.Model;

namespace EQT.Dal
{
    public partial class DaTuser:DaBase
    {
        /// <summary>
        /// 登入系统，状态2标识该用户可用
        /// </summary>
        private const string SQL_LOGIN = "SELECT COUNT(*) FROM TUser WHERE  UID=@UID AND PWD=@PWD AND Status=2";

   
        
        /// <summary>
        /// 查询用户与员工的对应信息
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public DataSet GetUser_Emp_Info(string deptid)
        {
            string strGetUser_Emp_Info = "SELECT * FROM V_User_Emploree WHERE Dept_ID"+ (deptid==""?" is null":"=@Dept_ID ");
            try
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strGetUser_Emp_Info);

                //设置参数:Dept_ID	
                if (!string.IsNullOrEmpty(deptid))
                {
                    db.AddInParameter(dbCommand, "Dept_ID", DbType.String, deptid == "" ? null : deptid);
                }
                return db.ExecuteDataSet(dbCommand);
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }

        /// <summary>
        /// 查询全部有效用户.
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllAvailableUsers()
        {
            string strGetUser_Emp_Info = "SELECT * FROM Tuser WHERE status=2 ";
            try
            {
                return db.ExecuteDataSet(CommandType.Text,strGetUser_Emp_Info);
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }

        /// <summary>
        /// 登入系统
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="user_pwd"></param>
        /// <returns></returns>
        public bool Login(string user_id, string user_pwd)
        {
            int rc = 0;
            DbCommand cmd= db.GetSqlStringCommand(SQL_LOGIN);
            db.AddInParameter(cmd, "UID", DbType.String, user_id);
            db.AddInParameter(cmd, "PWD", DbType.String, user_pwd);
            object obj= db.ExecuteScalar(cmd);
            if (obj != null && obj != DBNull.Value)
            {
                rc = Convert.ToInt32(obj);                
            }
            return rc > 0;

        }

        /// <summary>
        /// 用户上下文
        /// </summary>
        /// <param name="user_id">用户ID</param>
        /// <returns>用户上下文对象</returns>
        public UserContext GetUserContext(string user_id)
        {
            MoTuser tuser= this.GetEntity(user_id);
            if (tuser != null)
            {
                UserContext uc = new UserContext();
                uc.TUser = tuser;

                if (!String.IsNullOrEmpty(tuser.Emp_Id))
                {
                    uc.Emp = new DaEmployee(db).GetEntity(tuser.Emp_Id);
                    uc.Dept = new DaDept(db).GetEntity(uc.Emp.Dept_Id);
                }
                
                DaUserRole daur = new DaUserRole(db);
                //目前支持1个用户1个角色
                List<MoUserRole> lur= daur.GetEntities(String.Format(" WHERE UID='{0}'", tuser.Uid));
                if (lur != null && lur.Count > 0)
                {
                    uc.UserRole = lur[0];
                    DaTrole datr = new DaTrole(db);
                    uc.TRole= datr.GetEntity(uc.RoleID);
                }
                return uc;
                
            }
            return null;
        }

        public int CreateUser(MoTuser mod,string roleID)
        {
            int rc = 0;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {
                        this.Add(mod, trans);
                        DaUserRole dauserrole = new DaUserRole(db);
                        dauserrole.Add(new MoUserRole(mod.Uid, roleID), trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    return 1;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        public int UpdateUser(MoTuser mod, string roleID,string oldRoleID)
        {

            string SQL_UPDATE = "UPDATE User_Role SET Role_ID='"+roleID+"' WHERE UID='"+mod.Uid+"' AND  Role_ID='"+oldRoleID +"'";

            int rc = 0;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {
                        db.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE);
                        this.Update(mod, trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    return 1;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        public int DeleteUser(string uid, string strRoleID)
        {
            int rc = 0;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {
                        DaUserRole dauserrole = new DaUserRole(db);
                        dauserrole.Delete(uid, strRoleID, trans);
                        this.Delete(uid, trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    return 1;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        public DataSet Query()
        { 
            string sql="SELECT t.*, u.Role_ID AS Role_ID, r.Role_Name AS Role_Name"+
                        " FROM TUser t LEFT OUTER JOIN"+
                        " User_Role u ON t.UID = u.UID LEFT OUTER JOIN"+
                        " TRole r ON u.Role_ID = r.Role_ID where t.status<>-1";
            try
            {
                return db.ExecuteDataSet(CommandType.Text, sql);

            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }
    }
}

